package com.express.e.root.dao.impl;

import com.express.e.root.bean.User;
import com.express.e.root.dao.ConsoleDao;
import com.express.e.root.utils.DBUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class ConsoleDaoImpl extends DBUtils implements ConsoleDao {
    @Override
    public Integer queryUserTotalNum() {
        Integer total = -1;
        String sql = "SELECT COUNT(*) AS total FROM USER WHERE isDeleted = 0;";
        try {
            resultSet = query(sql, null);
            while (resultSet.next()){
                total = resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public Integer queryUserRegisterLastDay() {
        Integer total = -1;
        String sql = "SELECT COUNT(*) AS total FROM USER WHERE isDeleted = 0 AND userRegisterTime >= DATE_ADD(NOW(),INTERVAL -1 DAY);";
        try {
            resultSet = query(sql, null);
            while (resultSet.next()){
                total = resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public Integer queryCourierNum() {
        Integer total = -1;
        String sql = "SELECT COUNT(*) AS total FROM admin WHERE isDeleted = 0 AND role !=0;";
        try {
            resultSet = query(sql, null);
            while (resultSet.next()){
                total = resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public Integer queryCourierRegisterLastDay() {
        Integer total = -1;
        String sql = "SELECT COUNT(*) AS total FROM admin WHERE isDeleted = 0 AND role !=0 AND lastTime >= DATE_ADD(NOW(),INTERVAL -1 DAY);";
        try {
            resultSet = query(sql, null);
            while (resultSet.next()){
                total = resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public Integer queryDeliveryNum() {
        Integer total = -1;
        String sql = "SELECT COUNT(*) AS total FROM delivery WHERE isDeleted = 0 ;";
        try {
            resultSet = query(sql, null);
            while (resultSet.next()){
                total = resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public Integer queryDeliveryRegisterLastDay() {
        Integer total = -1;
        String sql = "SELECT COUNT(*) AS total FROM delivery WHERE isDeleted = 0 AND stateChangeTime >= DATE_ADD(NOW(),INTERVAL -1 DAY);";
        try {
            resultSet = query(sql, null);
            while (resultSet.next()){
                total = resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public Integer queryDeliveryFetchNum() {
        Integer total = -1;
        String sql = "SELECT COUNT(*) AS total FROM delivery WHERE isDeleted = 0 AND deliveryState = 0;";
        try {
            resultSet = query(sql, null);
            while (resultSet.next()){
                total = resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public Integer queryDeliveryFetchLastDay() {
        Integer total = -1;
        String sql = "SELECT COUNT(*) AS total FROM delivery WHERE isDeleted = 0 AND deliveryState = 0  AND stateChangeTime >= DATE_ADD(NOW(),INTERVAL -1 DAY);";
        try {
            resultSet = query(sql, null);
            while (resultSet.next()){
                total = resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public List queryLazyList() {
        List lazery = new ArrayList<>();
        String sql = "SELECT COUNT(*) AS pesonalExpressNum,u.`userName` AS users FROM delivery AS d,4expresse.`user` AS u WHERE d.`deliveryAcceptor` = u.`userName` GROUP BY users ORDER BY pesonalExpressNum DESC;";
        try {
            resultSet = query(sql, null);
            while (resultSet.next()){
                User user = new User();
                user.setUserName(resultSet.getString("users"));
                user.setUserId(resultSet.getInt("pesonalExpressNum"));
                lazery.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return lazery;
    }

    @Override
    public List queryLazyListOnMonth() {
        List lazery = new ArrayList<>();
        String sql = "SELECT COUNT(*) AS pesonalExpressNum,u.`userName` AS users FROM delivery AS d,4expresse.`user` AS u WHERE d.`deliveryAcceptor` = u.`userName` AND d.`stateChangeTime` > DATE_ADD(NOW(),INTERVAL -1 MONTH) GROUP BY users ORDER BY pesonalExpressNum DESC;";
        try {
            resultSet = query(sql, null);
            while (resultSet.next()){
                User user = new User();
                user.setUserName(resultSet.getString("users"));
                user.setUserId(resultSet.getInt("pesonalExpressNum"));
                lazery.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return lazery;
    }

    @Override
    public List queryLazyListOnYear() {
        List lazery = new ArrayList<>();
        String sql = "\n" +
                "SELECT COUNT(*) AS pesonalExpressNum,u.`userName` AS users FROM delivery AS d,4expresse.`user` AS u WHERE d.`deliveryAcceptor` = u.`userName` AND d.`stateChangeTime` > DATE_ADD(NOW(),INTERVAL -1 YEAR) GROUP BY users ORDER BY pesonalExpressNum DESC;";
        try {
            resultSet = query(sql, null);
            while (resultSet.next()){
                User user = new User();
                System.out.println(resultSet.getString("users"));
                user.setUserName(resultSet.getString("users"));
                user.setUserId(resultSet.getInt("pesonalExpressNum"));
                lazery.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return lazery;
    }
}
